package com.mote;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

class DBhelper {
	
	private static final String MOTE_TABLE = "mote_data";
	private static final String MOTE_DEL_TABLE = "mote_del_data";

	private static final String USER_NAME = "user_name";
	private static final String ID = "id";
	private static final String ITEM = "item";
	private static final String COPY_TIME = "copy_time";
	
	private static String USER;
	
	private DatabaseHelper dbHelper;
	private SQLiteDatabase mSQLiteDatabase;
	
	public SQLiteDatabase getDatabase()
	{
		return mSQLiteDatabase;
	}
	
	public DBhelper(Context context, SharedPreferences settings, int clearDB) {
		dbHelper = new DatabaseHelper(context);
		
		mSQLiteDatabase = dbHelper.getWritableDatabase();
		
		USER = settings.getString("user", "");
		
		if (clearDB == 1) {
			//清空数据库
			clearDB(USER);	
		}
	}
	
	class DatabaseHelper extends SQLiteOpenHelper {

		private static final String DB_CONTENT_CREATE = "create table mote_data(_id INTEGER PRIMARY KEY, user_name varchar(120), id int, item text, copy_time datetime);";
		private static final String DB_DEL_CREATE = "create table mote_del_data(_id int, id int);";

		private static final String DB_NAME = "mote.db";
		private static final int DB_VERSION = 1;

		private static final String MOTE_TABLE = "mote_data";
		private static final String MOTE_DEL_TABLE = "mote_del_data";

		public DatabaseHelper(Context context) {

			super(context, DB_NAME, null, DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

			// 创建数据库
			db.execSQL(DB_CONTENT_CREATE);
			db.execSQL(DB_DEL_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + MOTE_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + MOTE_DEL_TABLE);

			onCreate(db);
		}
	}
	
	// 插入一条数据 
	public long insertData(int id, String item, String copy_time) {
		ContentValues initialValues = new ContentValues();

		//
		initialValues.put(USER_NAME, USER);
		initialValues.put(ID, id);
		initialValues.put(ITEM, item);
		initialValues.put(COPY_TIME, copy_time);

		long res = mSQLiteDatabase.insert(MOTE_TABLE, null, initialValues);

		return res;
	}
	
	
	//根据_id查询某一个mote的内容
	public Cursor query(String tableName, long id)
	{
		Cursor cur = mSQLiteDatabase.query(tableName,
				new String[] { ITEM }, "_id=" + id, null, null, null, null);
		
		return cur;
	}
	
	//得到表的游标
	public Cursor fetchAllDataCur() {

		try {

			String content = "substr(" + ITEM + ", 0, 50)";
			
			//
			Cursor cur = mSQLiteDatabase.query("mote_data", new String[] { "_id", ID,
					content, COPY_TIME }, "user_name='" + USER + "'", null,
					null, null, "copy_time desc");
			
			return cur;
		}catch (Exception e) {
			String str = e.getMessage();
		}
		
		return null;
	}
	
	public void clearDB(String userName)
	{
		try {
			mSQLiteDatabase.execSQL("delete from " + MOTE_TABLE +" where user_name='"+userName+"'");	
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//删除一条记录
	public void delData(long _id) {

		try {
			// 得到和_id关联的id
			Cursor cursor = mSQLiteDatabase.query(MOTE_TABLE,
					new String[] { ID }, "_id=" + _id, null, null, null, null);

			if (cursor != null) {
				if (cursor.moveToFirst()) {

					int id = cursor.getInt(0);

					// 先删除mote
					long res = mSQLiteDatabase.delete(MOTE_TABLE, "id=" + id,
							null);

					if (res == 0) {
						return;
					}

					// 接着删除的mote保存到删除数据库中
					ContentValues initialValues = new ContentValues();

					//
					initialValues.put(ID, id);

					//
					res = mSQLiteDatabase.insert(MOTE_DEL_TABLE, null,
							initialValues);

				}
			}
		} catch (Exception e) {
			String str = e.getMessage();
		}

		return;
	}
}